﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spIO_ShrinkFile]
#-- Purpose:		Shrinks a File based on parameter criteria
#--	Last Update:	10/23/2015
#--					For a complete history - please review comments in Version
#--					control.
#-- Called By:		Scheduled Job
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spIO_ShrinkFile]
(
	@database_name			sysname,
	@only_auto_grow			bit				= 1,
	@minimum_size_MB		int				= 5,
	@is_type_log_file		bit				= 1,
	@print_query			bit				= 0,
	@execute_query			bit				= 1
)
WITH EXECUTE AS OWNER
AS

SET NOCOUNT ON

--- Determine if the user is authorized to do restores
DECLARE	@sError varchar(1000), @has_rights bit
EXEC [dbo].[spRest_IsAllowed] @database_name = @database_name, @is_allowed = @has_rights OUTPUT
IF @has_rights = 0
  BEGIN
	SELECT	@sError = 'You (' + ORIGINAL_LOGIN() + ') are not authorized to restore the database ' + @database_name
	RAISERROR (@sError, 16, 1)
	RETURN
  END

--- Declare Local Variables
DECLARE @sSQL nvarchar(max)

--- Build the Query
SELECT	@sSQL = '
USE [' + @database_name + ']; 
DECLARE @fileid int; 

DECLARE crs CURSOR FOR 
	SELECT	file_id 
	FROM	[master].[sys].[master_files]
	WHERE	database_id = DB_ID()'

--* Log File or Data File
IF ISNULL(@is_type_log_file, 1) = 1
	SELECT	@sSQL = @sSQL + '
			AND type = 1'
ELSE
	SELECT	@sSQL = @sSQL + '
			AND type <> 1'

--* File is set to auto-grow
IF @only_auto_grow = 1
	SELECT	@sSQL = @sSQL + '
			AND CAST(state+growth as bit) = 1	--- IsGrowthEnabled'

--* Filter to a file that is larger than the minimum size you wish to shrink to (don't attempt a shrink operation if unnecessary)
IF ISNULL(@minimum_size_MB, 0) > 0
	SELECT	@sSQL = @sSQL + '
			AND size > (' + CAST(@minimum_size_MB as nvarchar(20)) + '*1024/8)  --- needs to be larger than the ' + CAST(@minimum_size_MB as nvarchar(20)) + ' MB shrink attempt'

SELECT	@sSQL = @sSQL + '
	; 
OPEN crs; 
FETCH NEXT FROM crs INTO @fileid; 
WHILE @@FETCH_STATUS <> -1 
  BEGIN 
	PRINT ''DBCC SHRINKFILE (@fileid, ' + CAST(@minimum_size_MB as nvarchar(20)) + ');''
	DBCC SHRINKFILE (@fileid, ' + CAST(@minimum_size_MB as nvarchar(20)) + '); 
	FETCH NEXT FROM crs INTO @fileid; 
  END; 
CLOSE crs; 
DEALLOCATE crs;
'

--- Print the Query
IF ISNULL(@print_query, 0) = 1
	PRINT	@sSQL

--- Run the Query
IF ISNULL(@execute_query, 1) = 1
	EXEC	sp_executesql @sSQL

SET NOCOUNT OFF
